
alter  function f_finmsproduct(
    @bnyear integer,
    @bnmonth integer,
    @enyear integer,
    @enmonth integer,
    @bsubcode varchar(40) ,
    @esubcode varchar(40) ,
    @nsublevels integer,
    @esublevels integer,
    @productid integer,
    @nmoneyid integer,
    @filid integer)
returns @local_tab table(
    nyear integer,
    nmonth integer,
    subid integer,
    moneyid integer,
    bmbala decimal(18,2),
    bysumdebit decimal(18,2),
    bysumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsum decimal(18,2),
    bmqtybala decimal(18,2),
    byqtysumdebit decimal(18,2),
    byqtysumcredit decimal(18,2),
    tmqtysumdebit decimal(18,2),
    tmqtysumcredit decimal(18,2)
)
as
begin

  declare @tempsublevel integer

  declare @fbmbala     decimal(18,2)
  declare @fbysumdebit decimal(18,2)
  declare @fbysumcredit decimal(18,2)
  declare @ftmsumdebit decimal(18,2)
  declare @ftmsumcredit decimal(18,2)
  declare @ftmsum      decimal(18,2)

  declare
    @nyear integer,
    @nmonth integer,
    @subid integer,
    @moneyid integer,
    @bmbala decimal(18,2),
    @bysumdebit decimal(18,2),
    @bysumcredit decimal(18,2),
    @tmsumdebit decimal(18,2),
    @tmsumcredit decimal(18,2),
    @tmsum decimal(18,2),
    @bmqtybala decimal(18,2),
    @byqtysumdebit decimal(18,2),
    @byqtysumcredit decimal(18,2),
    @tmqtysumdebit decimal(18,2),
    @tmqtysumcredit decimal(18,2)

  set @moneyid=@nmoneyid

  if (@bsubcode is null or @bsubcode='')
    set @bsubcode='0'
  if (@esubcode is null or @esubcode='')
    set @esubcode='9'
  
  if (@nmoneyid=-99)
  begin

    insert into @local_tab(     
      nyear,
      nmonth,
      subid,
      moneyid,
      bmbala,
      bysumdebit,
      bysumcredit,
      tmsumdebit,
      tmsumcredit,
      tmsum,
      bmqtybala,
      byqtysumdebit,
      byqtysumcredit,
      tmqtysumdebit,
      tmqtysumcredit
    ) 
    select d.nyear,d.nmonth,s.subjectid,@moneyid as moneyid,
      (s.isdebit*2-1)*sum(d.bmbala) as bmbala,sum(bysumdebit) as bysumdebit,sum(bysumcredit) as bysumcredit,
      sum(tmsumdebit) as tmsumdebit,sum(tmsumcredit) as tmsumcredit,(s.isdebit*2-1)*sum(tmsum) as tmsum,
      (s.isdebit*2-1)*sum(d.bmqtybala) as bmqtybala,sum(byqtysumdebit) as byqtysumdebit,sum(byqtysumcredit) as byqtysumcredit,
      sum(tmqtysumdebit) as tmqtysumdebit,sum(tmqtysumcredit) as tmqtysumcredit
    from dbo.f_finmsproductdtl(
      @bnyear ,
      @bnmonth,
      @enyear ,
      @enmonth ,
      @nsublevels ,
      @productid,
      @filid) d
    inner join tfd_subject s on d.subcode=s.subcode
    group by d.nyear,d.nmonth,s.subjectid,s.subcode,s.sublevel,s.isdebit
    having s.subcode>=@bsubcode and s.subcode<=@esubcode+'z' and
      s.sublevel>=@nsublevels and s.sublevel<=@esublevels
  end
  else
  begin
    declare local_cursor cursor local
    for select d.nyear,d.nmonth,s.subjectid,d.moneyid,s.sublevel,
      (s.isdebit*2-1)*sum(d.bmbala) as bmbala,sum(bysumdebit) as bysumdebit,sum(bysumcredit) as bysumcredit,
      sum(tmsumdebit) as tmsumdebit,sum(tmsumcredit) as tmsumcredit,(s.isdebit*2-1)*sum(tmsum) as tmsum,
      (s.isdebit*2-1)*sum(d.fbmbala) as fbmbala,sum(fbysumdebit) as fbysumdebit,sum(fbysumcredit) as fbysumcredit,
      sum(ftmsumdebit) as ftmsumdebit,sum(ftmsumcredit) as ftmsumcredit,(s.isdebit*2-1)*sum(ftmsum) as ftmsum,
      (s.isdebit*2-1)*sum(d.bmqtybala) as bmqtybala,sum(byqtysumdebit) as byqtysumdebit,sum(byqtysumcredit) as byqtysumcredit,
      sum(tmqtysumdebit) as tmqtysumdebit,sum(tmqtysumcredit) as tmqtysumcredit
         from dbo.f_finmsproductdtl(
          @bnyear ,
          @bnmonth,
          @enyear ,
          @enmonth ,
          @nsublevels ,
          @productid,
          @filid) d
        inner join tfd_subject s on d.subcode=s.subcode
        group by d.nyear,d.nmonth,s.subjectid,s.subcode,d.moneyid,s.sublevel,s.isdebit
        having s.subcode>=@bsubcode and s.subcode<=@esubcode+'z' and
          s.sublevel>=@nsublevels and s.sublevel<=@esublevels and d.moneyid=@nmoneyid
 
    open local_cursor
    fetch next from local_cursor into @nyear,@nmonth,@subid,@moneyid,@tempsublevel,
          @bmbala,@bysumdebit,@bysumcredit,
          @tmsumdebit,@tmsumcredit,@tmsum,
          @fbmbala,@fbysumdebit,@fbysumcredit,
          @ftmsumdebit,@ftmsumcredit,@ftmsum,
          @bmqtybala,@byqtysumdebit,@byqtysumcredit,
          @tmqtysumdebit,@tmqtysumcredit
   while @@fetch_status = 0
    begin
      set @moneyid=@nmoneyid         
      insert into @local_tab(
        nyear,
        nmonth,
        subid,
        moneyid,
        bmbala,
        bysumdebit,
        bysumcredit,
        tmsumdebit,
        tmsumcredit,
        tmsum,
        bmqtybala,
        byqtysumdebit,
        byqtysumcredit,
        tmqtysumdebit,
        tmqtysumcredit
      ) values (
        @nyear,
        @nmonth,
        @subid,
        @moneyid,
        @bmbala,
        @bysumdebit,
        @bysumcredit,
        @tmsumdebit,
        @tmsumcredit,
        @tmsum,
        @bmqtybala,
        @byqtysumdebit,
        @byqtysumcredit,
        @tmqtysumdebit,
        @tmqtysumcredit
      )
      if (@moneyid<>0)
      begin
        set @moneyid=-@nmoneyid

        set @bmbala      =@fbmbala
        set @bysumdebit  =@fbysumdebit
        set @bysumcredit =@fbysumcredit
        set @tmsumdebit  =@ftmsumdebit
        set @tmsumcredit =@ftmsumcredit
        set @tmsum       =@ftmsum
        insert into @local_tab(
          nyear,
          nmonth,
          subid,
          moneyid,
          bmbala,
          bysumdebit,
          bysumcredit,
          tmsumdebit,
          tmsumcredit,
          tmsum,
          bmqtybala,
          byqtysumdebit,
          byqtysumcredit,
          tmqtysumdebit,
          tmqtysumcredit
        ) values (
          @nyear,
          @nmonth,
          @subid,
          @moneyid,
          @bmbala,
          @bysumdebit,
          @bysumcredit,
          @tmsumdebit,
          @tmsumcredit,
          @tmsum,
          @bmqtybala,
          @byqtysumdebit,
          @byqtysumcredit,
          @tmqtysumdebit,
          @tmqtysumcredit
        )
      end
      fetch next from local_cursor into @nyear,@nmonth,@subid,@moneyid,@tempsublevel,
          @bmbala,@bysumdebit,@bysumcredit,
          @tmsumdebit,@tmsumcredit,@tmsum,
          @fbmbala,@fbysumdebit,@fbysumcredit,
          @ftmsumdebit,@ftmsumcredit,@ftmsum,
          @bmqtybala,@byqtysumdebit,@byqtysumcredit,
          @tmqtysumdebit,@tmqtysumcredit
    end
    close local_cursor
    deallocate local_cursor      
  end
  
  return
end




